#!/usr/bin/python
# -*- coding:utf-8 -*-
# @author  : micah
# @time    :  
# @function: 
# @version :


import pymysql
import requests


class Ali():
    def __init__(self):
        self.db = pymysql.connect(host='localhost', port=3305, user='root', password='123456', db='spiders10')
        self.cursor = self.db.cursor()
        # 获取到url地址
        self.url = 'https://talent.alibaba.com/position/search?_csrf=4b0f8905-52fa-4f7e-bc9a-46c2f24574e6'
        self.headers = {
            'cookie': 'cna=PUX6Gy+33X8CAa8AO11A5I6W; xlly_s=1; prefered-lang=zh; XSRF-TOKEN=4b0f8905-52fa-4f7e-bc9a-46c2f24574e6; SESSION=NEU1RDFDMzgzRjNGNzgzNEFGMzkxQTU3OTcyM0VFRTk=; tfstk=caLdB29ayADhwbDl7wngU94uOrtRaHVdEW68yExgO8nS9V4uzsYkoEevZD1P39hO.; l=fBrMihtqNPyVb_4vBO5Zlurza77OwQAfcsPzaNbMiIEGa1zVsh9scN5sdwYp-dtjgTfbSeKr24w81dhD7TUdgdOFCK2x5FKNXYpM-bpU-L5..; isg=BFhY8TVDiCZs5KSiAVdUaVqGKYbqQbzLR7TTaZJLmxMeLfAXO1JwWxxLZWUdPXSj',
            'referer': 'https://talent.alibaba.com/off-campus/position-list?lang=zh',
            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36',
        }

    def create_table(self):
        # 使用预处理语句创建表
        sql = """
                CREATE TABLE IF NOT EXISTS ali(
                    id int primary key auto_increment not null,
                    workLocations VARCHAR(255) NOT NULL,
                    name VARCHAR(255) NOT NULL,
                    requirement TEXT
                );
        """
        try:
            self.cursor.execute(sql)
            print("CREATE TABLE SUCCESS.")
        except Exception as e:
            print(f'CREATE TABLE FAILED,CASE{e}')

    # 发送请求，获取响应
    def get_data(self, page):
        data = {
            "channel": "group_official_site",
            "language": "zh",
            "batchId": "",
            "categories": "",
            "deptCodes": [],
            "key": "",
            "pageIndex": page,
            "pageSize": 10,
            "regions": "",
            "subCategories": ""
        }
        response = requests.post(url=self.url, headers=self.headers, json=data)
        return response.json()

    # 提取数据
    def parse_data(self, data):
        for node in data['content']['datas']:
            workLocations = ','.join(i for i in node['workLocations'])
            name = node['name']
            requirement = node["requirement"]
            self.save_data(workLocations, name, requirement)

    # 保存数据
    def save_data(self, workLocations, name, requirement):
        # SQL 插入语句
        sql = """
            INSERT INTO ali(id,workLocations,name,requirement) values(%s,%s,%s,%s)
        """
        try:
            self.cursor.execute(sql, (0, workLocations, name, requirement))
            # 提交到数据库执行
            self.db.commit()
            print('数据插入成功')
        except Exception as e:
            print('数据插入报错：{}'.format(e))
            # 如果错误进行回滚
            self.db.rollback()

    # 主函数调用
    def main(self):
        self.create_table()
        for page in range(1, 10):
            data = self.get_data(page)
            self.parse_data(data)


if __name__ == '__main__':
    al = Ali()
    al.main()
